package com.qcorm.core;

import org.apache.log4j.Logger;
public class SqlBuilder<T extends BaseModel> {
	
	static Logger logger = Logger.getLogger(SqlBuilder.class.getName());
	private static  final String WHERE_HOLDER = " WHERE ";
	private static  final String PARAMETER_HOLDER = " = ? ";
	/**
	 *
	 * @param item
	 * @return
	 */
	public String buildAddSQL(T item){
		//INSERT INTO `foodinfo` (`name`, `remark`, `picurl`, `datachange_createtime`, `isshow`) 
		//VALUES ('test', 'teset', 'test', '2016-12-22 17:20:25', 1)
		if (item == null || item.tableName == null || item.tableName == "") {
			return null;
		}
		StringBuilder sql = new StringBuilder(50)
			.append("/*"+item.authorInfo+"*/")
			.append("INSERT INTO "+item.tableName +"(" );
		
		StringBuilder columnNames = new StringBuilder(50);
		StringBuilder valueHolder = new StringBuilder(20).append(" VALUES (");
		for (DaoParameter p : item.parameters) {
			columnNames.append(p.getParameterName()+",");
			valueHolder.append("?").append(",");
		}
		columnNames = columnNames.deleteCharAt(columnNames.length()-1);
		valueHolder = valueHolder.deleteCharAt(valueHolder.length()-1);
		columnNames.append(")");
		valueHolder.append(")");
		sql.append(columnNames).append(valueHolder);

		logger.info("SQL:" + sql.toString());
		return sql.toString();
		
	}

	/**
	 *
	 * @param item
	 * @return
	 */
	public String buildDeleteSQL(T item){
		
		//DELETE FROM foodinfo WHERE foodID = 10
		if (item == null || item.tableName == null || item.tableName == "") {
			return null;
		}

		StringBuilder sql = new StringBuilder(50)
			.append("/*"+item.authorInfo+"*/")
			.append("DELETE FROM "+item.tableName);
		
		StringBuilder dbQueryWhere = null;

		for (DaoParameter p : item.parameters) {
			
			if (p.getIsWhereCondition() && p.getParameterName().equals(item.keyName) && dbQueryWhere == null) {
				dbQueryWhere = new StringBuilder(50);
				dbQueryWhere.append(WHERE_HOLDER).append(p.getParameterName()).append(PARAMETER_HOLDER);
			}
			
		}

		if (dbQueryWhere != null) {
			sql.append(dbQueryWhere);
		}
		logger.info("SQL:" + sql.toString());
		
		return sql.toString();
	}

	/**
	 *
	 * @param item
	 * @return
	 */
	public String buildUpdateSQL(T item) {
		if (item == null || item.tableName == null || item.tableName == "") {
			return null;
		}

		StringBuilder sql = new StringBuilder(50)
			.append("/*"+item.authorInfo+"*/")
			.append("UPDATE "+item.tableName +" SET ");
		StringBuilder dbQueryWhere = null;

		for (DaoParameter p : item.parameters) {
			if (p.getIsWhereCondition()) {
				if (dbQueryWhere == null) {
					dbQueryWhere = new StringBuilder(50);
					dbQueryWhere.append(WHERE_HOLDER).append(p.getParameterName()).append(PARAMETER_HOLDER);
					continue;
				}
				dbQueryWhere.append(" AND ").append(p.getParameterName()).append(PARAMETER_HOLDER);
			}else{
				sql.append(p.getParameterName() + "=?, ");
			}
			
		}
		sql = sql.deleteCharAt(sql.length()-2);

		if (dbQueryWhere != null) {
			sql.append(dbQueryWhere);
		}
		logger.info("SQL:" + sql.toString());
		
		return sql.toString();
	}


	/**
	 *
	 * @param item
	 * @return
	 */
	public String buildQuerySQL(T item) {

		if (item == null || item.tableName == null || item.tableName == "") {
			return null;
		}
		DaoParameter[] pList = new DaoParameter[item.parameters.size()];
		item.parameters.toArray(pList);
		if (pList.length == 0) {
			return null;
		}
		StringBuilder sql = new StringBuilder(50)
			.append("/*"+item.authorInfo+"*/")
			.append("SELECT ");
		
		StringBuilder dbQueryWhere = null;

		int topCount = item.topCount;
		if (topCount > 0 && "SqlServer".equals(item.dbType)) {
			sql.append(" TOP ").append(topCount).append(" ");
		}

		for (DaoParameter p : pList) {

			sql.append(p.getParameterName() +", ");

			if (p.getIsWhereCondition()) {

				if (dbQueryWhere == null) {
					dbQueryWhere = new StringBuilder(50);
					dbQueryWhere.append(WHERE_HOLDER).append(p.getParameterName()).append(PARAMETER_HOLDER);

					continue;
				}
				dbQueryWhere.append(" AND ").append(p.getParameterName()).append(PARAMETER_HOLDER);
			}
		}
		sql.deleteCharAt(sql.length()-2);

		sql.append(" FROM ").append(item.tableName);
		if("SqlServer".equals(item.dbType)){
			sql.append("(NOLOCK)");
		}

		if (dbQueryWhere != null) {
			sql.append(dbQueryWhere);
		}
		
		if (topCount > 0 && "MySql".equals(item.dbType)) {
			sql.append(" limit ").append(0).append(",").append(topCount);
		}

		logger.info("SQL:" + sql.toString());
		
		return sql.toString();
	}
}
